import datetime
import pymysql


class LianAreaPipeline(object):
    def open_spider(self, spider):  # 在爬虫开启的时候仅执行一次

        self.conn = pymysql.connect(host='localhost', port=3306, user='root', password='123456', database='lianjia',
                           charset='utf8')

    def close_spider(self, spider):  # 在爬虫关闭的时候仅执行一次
        self.conn.close()

    def process_item(self, item, spider):
        name = item['qu_name']
        print(name)
        cs = self.conn.cursor()
        sql = "insert into tb_areas(name) values(%s);"
        res = cs.execute(sql,[name])
        self.conn.commit()
        cs.close()

class LianJiedaoAreaPipeline(object):
    # def open_spider(self, spider):  # 在爬虫开启的时候仅执行一次
    #
    #     self.conn = pymysql.connect(host='localhost', port=3306, user='root', password='123456', database='lianjia',
    #                        charset='utf8')
    #
    # def close_spider(self, spider):  # 在爬虫关闭的时候仅执行一次
    #     self.conn.close()

    def process_item(self, item, spider):
        qu_name = item['qu_name']
        jiedao_name = item['jiedao_name']
        # cs = self.conn.cursor()
        # 查找街道ｉｄ

        # sql = "select id from tb_areas where name=%s;"
        # res = cs.execute(sql,[qu_name])
        # qu_id = cs.fetchall()[0][0]
        # sql = "insert into tb_areas(name,parent_id) values(%s,%s);"
        # cs.execute(sql, [jiedao_name,qu_id])
        # self.conn.commit()
        # cs.close()


class LianHousePipeline(object):
    def open_spider(self, spider):  # 在爬虫开启的时候仅执行一次

        self.conn = pymysql.connect(host='localhost', port=3306, user='root', password='123456', database='lianjia',
                           charset='utf8')

    def close_spider(self, spider):  # 在爬虫关闭的时候仅执行一次
        self.conn.close()

    def process_item(self, item, spider):
        print(item)
        cs = self.conn.cursor()
        qu_name = item['qu_name']
        sql = 'select id from tb_areas where name=%s;'
        cs.execute(sql,[qu_name])
        # 街道外键ｉｄ
        qu_name_id = cs.fetchall()[0][0]
        house_name = item['house_name']
        image_url = item['image_url']
        detail_url = item['detail_url']
        village_name =  item['village_name']
        house_layout = item['house_layout']
        house_space = item['house_space']
        total_price = item['total_price']
        unit_price = item['unit_price']
        update_time = datetime.datetime.now()
        create_time = datetime.datetime.now()
        sql = 'insert into contents_houseinfo (`jiedao_name_id`,`house_name`,`image_url`,`detail_url`,`village_name`,`house_layout`,`house_space`,`total_price`,`unit_price`,update_time,create_time) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);'
        params = [qu_name_id,house_name,image_url,detail_url,
                  village_name,house_layout,house_space,total_price,unit_price,update_time,create_time]
        cs.execute(sql,params)
        self.conn.commit()
        cs.close()



# insert into contents_houseinfo (`jiedao_name_id`,`house_name`,`image_url`) values(134,'百盛旁边 地铁站口 交通便利生活设施完善','image1.ljcdn.com/110000-inspection/pc1_x4ARVRWhe_1.jpg.296x216.jpg');
# ,`detail_url`,`village_name`,`house_layout`,`house_space`,`total_price`,`unit_price`